
const express = require('express');
const axios = require('axios');
const router = express.Router();

// Middleware to check authentication
const authenticateToken = (req, res, next) => {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];

  if (!token) {
    return res.status(401).json({ error: 'Access token required' });
  }

  const jwt = require('jsonwebtoken');
  jwt.verify(token, process.env.JWT_SECRET || 'your-secret-key', (err, user) => {
    if (err) return res.status(403).json({ error: 'Invalid token' });
    req.user = user;
    next();
  });
};

// Helper function to call DeepSeek API with better error handling (Synced with ai-suggestions.js)
async function generateAIContent(prompt, maxRetries = 3, maxTokens = 2000) {
  let lastError;

  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      console.log(`AI API Call Attempt ${attempt}/${maxRetries}`);

      const response = await axios.post('https://api.deepseek.com/v1/chat/completions', {
        model: 'deepseek-chat',
        messages: [
          {
            role: 'system',
            content: 'You are a professional AI assistant specializing in project management and work reporting. Your output should be professional, concise, and action-oriented.'
          },
          { role: 'user', content: prompt }
        ],
        max_tokens: maxTokens,
        temperature: 0.5
      }, {
        headers: {
          'Authorization': `Bearer ${process.env.DEEPSEEK_API_KEY}`,
          'Content-Type': 'application/json'
        },
        timeout: 30000
      });

      return response.data.choices[0].message.content;

    } catch (error) {
      lastError = error;
      console.error(`AI API Call Failed (${attempt}/${maxRetries}):`, error.message);

      if (error.response?.status === 429) {
        const waitTime = 2000 * attempt;
        await new Promise(resolve => setTimeout(resolve, waitTime));
        continue;
      }

      if (error.response?.status === 401) {
        throw new Error('Invalid API Key');
      }
    }
  }

  throw lastError || new Error('AI Service Unavailable');
}

// --- Daily Report Routes ---

// Get daily reports
router.get('/daily', authenticateToken, (req, res) => {
  const { weekly_plan_id, start_date, end_date } = req.query;
  const db = req.db;

  let query = 'SELECT * FROM daily_reports WHERE user_id = ?';
  let params = [req.user.userId];

  if (weekly_plan_id) {
    query += ' AND weekly_plan_id = ?';
    params.push(weekly_plan_id);
  }

  if (start_date && end_date) {
    query += ' AND date BETWEEN ? AND ?';
    params.push(start_date);
    params.push(end_date);
  }

  query += ' ORDER BY date DESC';

  db.all(query, params, (err, reports) => {
    if (err) {
      console.error('获取日报失败:', err);
      return res.status(500).json({ error: 'Database error' });
    }
    res.json(reports);
  });
});

// Create or Update daily report
router.post('/daily', authenticateToken, (req, res) => {
  const { weekly_plan_id, date, content } = req.body;
  const db = req.db;

  if (!date || !content) {
    return res.status(400).json({ error: 'Date and content are required' });
  }

  // Check if report already exists for this date and plan (or just date for this user?)
  // Usually one daily report per day per user.
  // But maybe linked to a plan.
  // Let's assume one per day per user for simplicity, or allow multiple if linked to different plans?
  // The user said "open weekly plan tab... report daily situation".
  // Let's enforce unique date per user for now to keep it simple, or unique date+plan.
  // Let's do unique date per user.

  db.get(
    'SELECT id FROM daily_reports WHERE user_id = ? AND date = ?',
    [req.user.userId, date],
    (err, existing) => {
      if (err) return res.status(500).json({ error: 'Database error' });

      if (existing) {
        // Update
        db.run(
          'UPDATE daily_reports SET content = ?, weekly_plan_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
          [content, weekly_plan_id || null, existing.id],
          function (err) {
            if (err) return res.status(500).json({ error: 'Database error' });

            res.json({ id: existing.id, message: 'Daily report updated' });
          }
        );
      } else {
        // Create
        db.run(
          'INSERT INTO daily_reports (user_id, weekly_plan_id, date, content) VALUES (?, ?, ?, ?)',
          [req.user.userId, weekly_plan_id || null, date, content],
          function (err) {
            if (err) return res.status(500).json({ error: 'Database error' });

            res.status(201).json({ id: this.lastID, message: 'Daily report created' });
          }
        );
      }
    }
  );
});


// Generate weekly report content from daily reports and plan
router.post('/weekly/generate-content', authenticateToken, async (req, res) => {
  const { planId, week, month, year } = req.body;
  const db = req.db;

  try {
    // 1. Fetch Weekly Plan
    let plan = null;
    if (planId) {
      plan = await new Promise((resolve) => {
        db.get('SELECT * FROM weekly_plans WHERE id = ? AND user_id = ?', [planId, req.user.userId], (err, row) => resolve(row));
      });
    }

    // 2. Fetch Daily Reports for this week
    // We need to calculate start and end date of the week
    // Assuming ISO week date or similar. 
    // For simplicity, let's just fetch all daily reports that fall into the week.
    // But we only have week number. We need to calculate dates.
    // Or we can just fetch all daily reports for the user and filter by date in JS (if not too many), or rely on planId if daily reports are linked to plan.
    // If daily reports are linked to planId, it's easy.

    let dailyReports = [];
    if (planId) {
      dailyReports = await new Promise((resolve, reject) => {
        db.all('SELECT * FROM daily_reports WHERE weekly_plan_id = ? ORDER BY date ASC', [planId], (err, rows) => {
          if (err) reject(err);
          else resolve(rows || []);
        });
      });
    }

    // If no reports found by planId, try by date range if we can calculate it.
    // Since we don't have a robust date util here, let's rely on planId for now, 
    // OR the frontend should pass the date range.
    // Let's assume frontend passes date range or we rely on planId.
    // Actually, the user said "open weekly plan... choose to report daily". 
    // So daily reports should be linked to the plan.

    if (dailyReports.length === 0) {
      // Fallback: try to find reports that might belong to this week based on creation time?
      // No, let's just return what we have.
    }

    // 3. Generate Content
    const planContent = plan ? `Plan Title: ${plan.title}\nObjectives: ${plan.objectives}` : '';
    const dailyContent = dailyReports.map(r => `Date ${r.date}: ${r.content}`).join('\n');

    if (!planContent && !dailyContent) {
      return res.json({
        achievements: '',
        challenges: '',
        next_week_plan: ''
      });
    }

    const prompt = `
    Task: Generate a Weekly Report based on the following inputs.
    
    ${planContent}
    
    Daily Work Logs:
    ${dailyContent}
    
    Requirements:
    1. Summarize "achievements" (completed work) based on daily logs and plan objectives.
    2. Identify "challenges" (issues encountered). If none are explicitly mentioned, infer potential challenges based on the nature of work or suggest "无明显阻碍，工作进展顺利".
    3. Suggest "next_week_plan" based on unfinished tasks or logical next steps. If not clear, suggest "继续推进当前项目，按计划执行".
    4. **IMPORTANT**: The output MUST be in **Simplified Chinese**.
    5. **IMPORTANT**: The output MUST be a valid JSON object.
    
    Return Format (JSON):
    {
      "achievements": "...",
      "challenges": "...",
      "next_week_plan": "..."
    }
    `;

    const aiResponse = await generateAIContent(prompt);
    let result = { achievements: '', challenges: '', next_week_plan: '' };

    try {
      // Try to find JSON in the response if it contains markdown code blocks
      const jsonMatch = aiResponse.match(/\{[\s\S]*\}/);
      const jsonString = jsonMatch ? jsonMatch[0] : aiResponse;
      result = JSON.parse(jsonString);
    } catch (e) {
      console.warn('AI JSON Parse Failed, returning raw text in achievements');
      // Fallback: put everything in achievements if parsing fails
      result.achievements = aiResponse;
    }

    res.json(result);

  } catch (error) {
    console.error('Generate weekly content error:', error);
    res.status(500).json({ error: 'Failed to generate content' });
  }
});

// --- Weekly Report Routes ---

// Submit weekly report
router.post('/weekly', authenticateToken, async (req, res) => {
  const { week, month, year, achievements, challenges, next_week_plan, weekly_plan_id } = req.body;
  const db = req.db;

  try {
    // Generate highlights using AI
    const prompt = `
    Task: Generate 3-5 concise professional highlights based on the following weekly report.
    
    Achievements: ${achievements}
    Challenges: ${challenges}
    
    Format: Bullet points, plain text, no markdown formatting.
    **IMPORTANT**: The output MUST be in **Simplified Chinese**.
    `;

    let highlights = '';
    try {
      highlights = await generateAIContent(prompt);
    } catch (e) {
      console.warn('AI Highlight Generation Failed, using default');
      highlights = 'AI generation unavailable. Please add highlights manually.';
    }

    // If weekly_plan_id is provided, verify it belongs to the user
    if (weekly_plan_id) {
      const plan = await new Promise((resolve, reject) => {
        db.get(
          'SELECT id FROM weekly_plans WHERE id = ? AND user_id = ?',
          [weekly_plan_id, req.user.userId],
          (err, row) => {
            if (err) reject(err);
            else resolve(row);
          }
        );
      });

      if (!plan) {
        return res.status(400).json({ error: 'Invalid weekly_plan_id or unauthorized access' });
      }
    }

    db.run(
      'INSERT INTO weekly_reports (user_id, week, month, year, achievements, challenges, next_week_plan, highlights, weekly_plan_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [req.user.userId, week, month, year, achievements, challenges, next_week_plan, highlights, weekly_plan_id || null],
      function (err) {
        if (err) {
          console.error('创建周报错误:', err);
          return res.status(500).json({ error: 'Database error', details: err.message });
        }

        console.log('周报创建成功, ID:', this.lastID, weekly_plan_id ? `关联计划ID: ${weekly_plan_id}` : '未关联计划');

        // 1. Update Weekly Plan status to completed
        if (weekly_plan_id) {
          db.run(
            'UPDATE weekly_plans SET status = ? WHERE id = ? AND user_id = ?',
            ['completed', weekly_plan_id, req.user.userId],
            (updateErr) => {
              if (updateErr) console.error('Failed to update weekly plan status:', updateErr);
            }
          );
        }

        // 2. Update Monthly Plan status to in_progress
        db.run(
          "UPDATE monthly_plans SET status = 'in_progress' WHERE user_id = ? AND year = ? AND month = ? AND status = 'pending'",
          [req.user.userId, year, month],
          (updateErr) => {
            if (updateErr) console.error('Failed to update monthly plan status:', updateErr);
            else console.log(`Updated monthly plan status to in_progress for ${year}-${month}`);
          }
        );

        // 3. Update Annual Plan status to in_progress
        db.run(
          "UPDATE annual_plans SET status = 'in_progress' WHERE user_id = ? AND year = ? AND status = 'pending'",
          [req.user.userId, year],
          (updateErr) => {
            if (updateErr) console.error('Failed to update annual plan status:', updateErr);
            else console.log(`Updated annual plan status to in_progress for ${year}`);
          }
        );

        res.status(201).json({
          id: this.lastID,
          highlights,
          weekly_plan_id: weekly_plan_id || null,
          message: 'Weekly report submitted successfully'
        });
      }
    );
  } catch (error) {
    console.error('处理周报提交错误:', error);
    res.status(500).json({ error: 'Error processing request', details: error.message });
  }
});

// Get available weekly plans for association
router.get('/weekly/available-plans', authenticateToken, (req, res) => {
  const { year, month, week } = req.query;
  const db = req.db;

  if (!year || !month || !week) {
    return res.status(400).json({ error: 'year, month, and week are required' });
  }

  // Get weekly plans for the specified period (entire month)
  db.all(
    `SELECT id, title, description, objectives, status, created_at, week 
     FROM weekly_plans 
     WHERE user_id = ? AND year = ? AND month = ?
     ORDER BY week ASC, created_at DESC`,
    [req.user.userId, year, month],
    (err, plans) => {
      if (err) {
        console.error('获取可关联计划错误:', err);
        return res.status(500).json({ error: 'Database error' });
      }

      res.json(plans || []);
    }
  );
});

// Update weekly report
router.put('/weekly/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const { summary, highlights, achievements, challenges, next_week_plan } = req.body;
  const db = req.db;

  const updates = [];
  const params = [];

  if (summary !== undefined) {
    updates.push('summary = ?');
    params.push(summary);
  }
  if (highlights !== undefined) {
    updates.push('highlights = ?');
    params.push(highlights);
  }
  if (achievements !== undefined) {
    updates.push('achievements = ?');
    params.push(achievements);
  }
  if (challenges !== undefined) {
    updates.push('challenges = ?');
    params.push(challenges);
  }
  if (next_week_plan !== undefined) {
    updates.push('next_week_plan = ?');
    params.push(next_week_plan);
  }

  if (updates.length === 0) {
    return res.status(400).json({ error: 'No fields to update' });
  }

  params.push(id);
  params.push(req.user.userId);

  const query = `UPDATE weekly_reports SET ${updates.join(', ')} WHERE id = ? AND user_id = ?`;

  console.log('更新周报 SQL:', query);
  console.log('更新参数:', params);

  db.run(query, params, function (err) {
    if (err) {
      console.error('更新周报数据库错误:', err);
      console.error('SQL语句:', query);
      console.error('参数:', params);
      return res.status(500).json({
        error: 'Database error',
        details: err.message,
        sql: query
      });
    }
    if (this.changes === 0) {
      console.log('未找到要更新的周报, ID:', id, 'User ID:', req.user.userId);
      return res.status(404).json({ error: 'Report not found' });
    }
    console.log('周报更新成功, ID:', id, '变更行数:', this.changes);
    res.json({ message: 'Weekly report updated successfully' });
  });
});

// Delete weekly report
router.delete('/weekly/:id', authenticateToken, (req, res) => {
  const { id } = req.params;
  const db = req.db;

  db.run(
    'DELETE FROM weekly_reports WHERE id = ? AND user_id = ?',
    [id, req.user.userId],
    function (err) {
      if (err) {
        return res.status(500).json({ error: 'Database error' });
      }
      if (this.changes === 0) {
        return res.status(404).json({ error: 'Report not found or unauthorized' });
      }
      res.json({ message: 'Weekly report deleted successfully' });
    }
  );
});

// Get weekly reports
router.get('/weekly', authenticateToken, (req, res) => {
  const db = req.db;
  const userId = req.user.role === 'manager' ? req.query.userId : req.user.userId;
  const year = req.query.year;

  let query = `
    SELECT wr.*, wp.title as plan_title 
    FROM weekly_reports wr 
    LEFT JOIN weekly_plans wp ON wr.weekly_plan_id = wp.id 
    WHERE wr.user_id = ?
  `;
  let params = [userId || req.user.userId];

  if (year) {
    query += ' AND wr.year = ?';
    params.push(year);
  }

  query += ' ORDER BY wr.year DESC, wr.month DESC, wr.week DESC';

  db.all(query, params, (err, reports) => {
    if (err) {
      return res.status(500).json({ error: 'Database error' });
    }
    res.json(reports);
  }
  );
});

// Generate weekly report from plan (AI Assisted)
router.post('/weekly/from-plan', authenticateToken, async (req, res) => {
  const { planId, week, month, year, achievements, challenges, next_week_plan } = req.body;
  const db = req.db;

  try {
    // Generate highlights using AI
    const prompt = `
    Task: Generate 3-5 concise professional highlights based on the following weekly report content derived from a plan.
    
    Work Done: ${achievements}
    Issues: ${challenges}
    
    Format: Bullet points, plain text.
    **IMPORTANT**: The output MUST be in **Simplified Chinese**.
    `;

    let highlights = '';
    try {
      highlights = await generateAIContent(prompt);
    } catch (e) {
      highlights = 'AI generation unavailable.';
    }

    db.run(
      'INSERT INTO weekly_reports (user_id, week, month, year, achievements, challenges, next_week_plan, highlights) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [req.user.userId, week, month, year, achievements, challenges, next_week_plan, highlights],
      function (err) {
        if (err) {
          return res.status(500).json({ error: 'Database error' });
        }

        // 1. Update Weekly Plan status to completed
        db.run(
          'UPDATE weekly_plans SET status = ? WHERE id = ? AND user_id = ?',
          ['completed', planId, req.user.userId],
          function (updateErr) {
            if (updateErr) console.error('Failed to update plan status:', updateErr);
          }
        );

        // 2. Update Monthly Plan status to in_progress
        db.run(
          "UPDATE monthly_plans SET status = 'in_progress' WHERE user_id = ? AND year = ? AND month = ? AND status = 'pending'",
          [req.user.userId, year, month],
          (updateErr) => {
            if (updateErr) console.error('Failed to update monthly plan status:', updateErr);
          }
        );

        // 3. Update Annual Plan status to in_progress
        db.run(
          "UPDATE annual_plans SET status = 'in_progress' WHERE user_id = ? AND year = ? AND status = 'pending'",
          [req.user.userId, year],
          (updateErr) => {
            if (updateErr) console.error('Failed to update annual plan status:', updateErr);
          }
        );

        res.status(201).json({ id: this.lastID, highlights, message: 'Weekly report submitted successfully' });
      }
    );
  } catch (error) {
    res.status(500).json({ error: 'Error generating AI insights' });
  }
});

// --- Monthly Report Routes ---

// Generate monthly report (Cascade: Weekly -> Monthly)
router.post('/monthly/generate', authenticateToken, async (req, res) => {
  const { month, year } = req.body;
  const db = req.db;

  try {
    // Get all weekly reports for the month
    db.all(
      'SELECT * FROM weekly_reports WHERE user_id = ? AND month = ? AND year = ?',
      [req.user.userId, month, year],
      async (err, weeklyReports) => {
        if (err) return res.status(500).json({ error: 'Database error' });

        // Combine weekly reports
        const allAchievements = weeklyReports.map(r => `${r.achievements}`).join('\n');
        const allChallenges = weeklyReports.map(r => `${r.challenges}`).join('\n');
        const allHighlights = weeklyReports.map(r => r.highlights || '').join('\n');

        // Generate AI summary
        const prompt = `
        Task: Generate a comprehensive Monthly Work Summary based on the weekly reports provided.
        
        Input Data (Achievements, Challenges, Highlights from various weeks):
        ${allAchievements}
        ${allChallenges}
        ${allHighlights}
        
        Requirements:
        1. **Synthesize** the information into a cohesive monthly narrative.
        2. **STRICTLY FORBIDDEN**: Do NOT mention specific weeks (e.g., "Week 1", "First Week", "In the second week", "第一周", "上旬" etc.). Treat the time period as a single continuous month.
        3. **Summary**: Write a professional summary of the month's work (approx 300 words). Focus on overall **Achievements** (成效) and progress. Merge related tasks into a holistic view.
        4. **Highlights**: Extract 3-5 key highlights/achievements (亮点) as bullet points.
        5. **IMPORTANT**: The output MUST be in **Simplified Chinese**.
        6. **IMPORTANT**: The output MUST be a valid JSON object.
        
        Return Format (JSON):
        {
          "summary": "...",
          "highlights": "..."
        }
        `;

        let summary = '';
        let highlights = '';

        try {
          const aiResponse = await generateAIContent(prompt, 3, 1000);
          // Attempt to parse JSON
          try {
            // Try to find JSON in the response if it contains markdown code blocks
            const jsonMatch = aiResponse.match(/\{[\s\S]*\}/);
            const jsonString = jsonMatch ? jsonMatch[0] : aiResponse;
            const json = JSON.parse(jsonString);

            summary = json.summary;
            highlights = Array.isArray(json.highlights) ? json.highlights.join('\n') : json.highlights;
          } catch (e) {
            console.warn('AI JSON Parse Failed for Monthly Report, using raw text');
            // Fallback if text
            summary = aiResponse;
            highlights = 'Extracted from summary';
          }
        } catch (e) {
          summary = 'AI Generation Failed';
          highlights = '';
        }

        // Upsert Monthly Report
        db.get(
          'SELECT id FROM monthly_reports WHERE user_id = ? AND month = ? AND year = ?',
          [req.user.userId, month, year],
          (err, existing) => {
            if (err) return res.status(500).json({ error: 'Database error' });

            const handleMonthlyReportCreated = (monthlyReportId) => {
              // Link all weekly reports to this monthly report
              const weeklyReportIds = weeklyReports.map(r => r.id);

              console.log(`关联 ${weeklyReportIds.length} 个周报到月报 ID: ${monthlyReportId}`);

              const updatePromises = weeklyReportIds.map(reportId => {
                return new Promise((resolve, reject) => {
                  db.run(
                    'UPDATE weekly_reports SET monthly_report_id = ? WHERE id = ?',
                    [monthlyReportId, reportId],
                    (updateErr) => {
                      if (updateErr) {
                        console.error(`更新周报 ${reportId} 失败:`, updateErr);
                        reject(updateErr);
                      } else {
                        resolve();
                      }
                    }
                  );
                });
              });

              Promise.all(updatePromises)
                .then(() => {
                  console.log(`成功关联所有周报到月报 ${monthlyReportId}`);

                  // 1. Update Monthly Plan status to completed
                  db.run(
                    "UPDATE monthly_plans SET status = 'completed' WHERE user_id = ? AND year = ? AND month = ?",
                    [req.user.userId, year, month],
                    (updateErr) => {
                      if (updateErr) console.error('Failed to update monthly plan status:', updateErr);
                      else console.log(`Updated monthly plan status to completed for ${year}-${month}`);
                    }
                  );

                  // 2. Update Annual Plan status to in_progress
                  db.run(
                    "UPDATE annual_plans SET status = 'in_progress' WHERE user_id = ? AND year = ? AND status = 'pending'",
                    [req.user.userId, year],
                    (updateErr) => {
                      if (updateErr) console.error('Failed to update annual plan status:', updateErr);
                      else console.log(`Updated annual plan status to in_progress for ${year}`);
                    }
                  );

                  res.json({
                    id: monthlyReportId,
                    summary,
                    highlights,
                    linked_weekly_reports: weeklyReportIds.length,
                    message: existing ? 'Monthly report updated successfully' : 'Monthly report generated successfully'
                  });
                })
                .catch((updateErr) => {
                  console.error('关联周报失败:', updateErr);
                  // 即使关联失败，月报已创建，返回成功但带警告
                  res.json({
                    id: monthlyReportId,
                    summary,
                    highlights,
                    warning: 'Monthly report created but failed to link some weekly reports',
                    message: existing ? 'Monthly report updated' : 'Monthly report generated'
                  });
                });
            };

            if (existing) {
              db.run(
                'UPDATE monthly_reports SET summary = ?, highlights = ? WHERE id = ?',
                [summary, highlights, existing.id],
                function (err) {
                  if (err) return res.status(500).json({ error: 'Database error' });
                  handleMonthlyReportCreated(existing.id);
                }
              );
            } else {
              db.run(
                'INSERT INTO monthly_reports (user_id, month, year, summary, highlights) VALUES (?, ?, ?, ?, ?)',
                [req.user.userId, month, year, summary, highlights],
                function (err) {
                  if (err) return res.status(500).json({ error: 'Database error' });
                  handleMonthlyReportCreated(this.lastID);
                }
              );
            }
          }
        );
      }
    );
  } catch (error) {
    console.error('生成月报错误:', error);
    res.status(500).json({ error: 'Error generating monthly report' });
  }
});

// Get monthly reports
router.get('/monthly', authenticateToken, (req, res) => {
  const db = req.db;
  const userId = req.user.role === 'manager' ? req.query.userId : req.user.userId;
  const year = req.query.year;

  let query = 'SELECT * FROM monthly_reports WHERE user_id = ?';
  let params = [userId || req.user.userId];

  if (year) {
    query += ' AND year = ?';
    params.push(year);
  }

  query += ' ORDER BY year DESC, month DESC';

  db.all(query, params, (err, reports) => {
    if (err) return res.status(500).json({ error: 'Database error' });
    res.json(reports);
  }
  );
});

// --- Annual Report Routes ---

// Generate annual report (Cascade: Monthly -> Annual)
router.post('/annual/generate', authenticateToken, async (req, res) => {
  const { year } = req.body;
  const db = req.db;

  try {
    // Get all monthly reports for the year
    db.all(
      'SELECT * FROM monthly_reports WHERE user_id = ? AND year = ?',
      [req.user.userId, year],
      async (err, monthlyReports) => {
        if (err) return res.status(500).json({ error: 'Database error' });

        if (monthlyReports.length === 0) {
          return res.status(400).json({ error: 'No monthly reports found for this year' });
        }

        // Combine monthly reports
        const allSummaries = monthlyReports.map(r => `Month ${r.month}: ${r.summary}`).join('\n\n');
        const allHighlights = monthlyReports.map(r => r.highlights || '').join('\n');

        // Generate AI annual summary
        const prompt = `
        Task: Generate a comprehensive Annual Work Report based on the monthly summaries provided.
        
        Monthly Summaries:
        ${allSummaries}
        
        Yearly Highlights:
        ${allHighlights}
        
        Requirements:
        1. "summary": A high-level executive summary of the year (approx 300 words).
        2. "achievements": Key achievements and milestones reached.
        3. "challenges": Major challenges faced and how they were overcome.
        4. "highlights": A list of top 5-7 highlights for the year.
        
        Return Format: JSON with keys "summary", "achievements", "challenges", "highlights".
        `;

        let annualData = {
          summary: '',
          achievements: '',
          challenges: '',
          highlights: ''
        };

        try {
          const aiResponse = await generateAIContent(prompt, 3, 2000);
          try {
            annualData = JSON.parse(aiResponse);
          } catch (e) {
            annualData.summary = aiResponse;
            annualData.achievements = 'Extracted from summary';
            annualData.challenges = 'Extracted from summary';
            annualData.highlights = 'Extracted from summary';
          }
        } catch (e) {
          console.error('AI Generation Failed:', e);
          annualData.summary = 'AI Generation Failed';
        }

        const { summary, achievements, challenges, highlights } = annualData;
        const highlightsStr = Array.isArray(highlights) ? highlights.join('\n') : highlights;
        const achievementsStr = Array.isArray(achievements) ? achievements.join('\n') : achievements;

        // Upsert Annual Report
        db.get(
          'SELECT id FROM annual_reports WHERE user_id = ? AND year = ?',
          [req.user.userId, year],
          (err, existing) => {
            if (err) return res.status(500).json({ error: 'Database error' });

            // Update Annual Plan status to completed
            db.run(
              "UPDATE annual_plans SET status = 'completed' WHERE user_id = ? AND year = ?",
              [req.user.userId, year],
              (updateErr) => {
                if (updateErr) console.error('Failed to update annual plan status:', updateErr);
                else console.log(`Updated annual plan status to completed for ${year}`);
              }
            );

            if (existing) {
              db.run(
                'UPDATE annual_reports SET summary = ?, achievements = ?, challenges = ?, highlights = ? WHERE id = ?',
                [summary, achievementsStr, challenges, highlightsStr, existing.id],
                function (err) {
                  if (err) return res.status(500).json({ error: 'Database error' });
                  res.json({ id: existing.id, summary, achievements: achievementsStr, challenges, highlights: highlightsStr, message: 'Annual report updated successfully' });
                }
              );
            } else {
              db.run(
                'INSERT INTO annual_reports (user_id, year, summary, achievements, challenges, highlights) VALUES (?, ?, ?, ?, ?, ?)',
                [req.user.userId, year, summary, achievementsStr, challenges, highlightsStr],
                function (err) {
                  if (err) return res.status(500).json({ error: 'Database error' });
                  res.status(201).json({ id: this.lastID, summary, achievements: achievementsStr, challenges, highlights: highlightsStr, message: 'Annual report generated successfully' });
                }
              );
            }
          }
        );
      }
    );
  } catch (error) {
    res.status(500).json({ error: 'Error generating annual report' });
  }
});

// Get annual reports
router.get('/annual', authenticateToken, (req, res) => {
  const db = req.db;
  const userId = req.user.role === 'manager' ? req.query.userId : req.user.userId;
  const year = req.query.year;

  let query = 'SELECT * FROM annual_reports WHERE user_id = ?';
  let params = [userId || req.user.userId];

  if (year) {
    query += ' AND year = ?';
    params.push(year);
  }

  query += ' ORDER BY year DESC';

  db.all(query, params, (err, reports) => {
    if (err) {
      return res.status(500).json({ error: 'Database error' });
    }
    // If querying by year, return single object if found, or null
    if (year) {
      res.json(reports.length > 0 ? reports[0] : null);
    } else {
      res.json(reports);
    }
  }
  );
});

// Generate weekly report from plan
router.post('/weekly/from-plan', authenticateToken, async (req, res) => {
  const { planId, week, month, year, achievements, challenges, next_week_plan } = req.body;
  const db = req.db;

  try {
    // Generate highlights using DeepSeek
    const prompt = `Based on these weekly achievements: ${achievements}, and challenges: ${challenges}, generate 3-5 concise work highlights.`;
    const highlights = await generateAIContent(prompt);

    db.run(
      'INSERT INTO weekly_reports (user_id, week, month, year, achievements, challenges, next_week_plan, highlights) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [req.user.userId, week, month, year, achievements, challenges, next_week_plan, highlights],
      function (err) {
        if (err) {
          return res.status(500).json({ error: 'Database error' });
        }

        // Update plan status to completed
        db.run(
          'UPDATE weekly_plans SET status = ? WHERE id = ? AND user_id = ?',
          ['completed', planId, req.user.userId],
          function (updateErr) {
            if (updateErr) {
              console.error('Failed to update plan status:', updateErr);
            }
          }
        );

        res.status(201).json({ id: this.lastID, highlights, message: 'Weekly report submitted successfully' });
      }
    );
  } catch (error) {
    res.status(500).json({ error: 'Error generating AI insights' });
  }
});

// Get AI work suggestions
router.post('/ai-suggestions', authenticateToken, async (req, res) => {
  const { workType, currentProgress, challenges } = req.body;

  try {
    const prompt = `For ${workType} work with current progress: ${currentProgress}, and challenges: ${challenges}, provide 3-5 specific, actionable next steps and suggestions.`;
    const suggestions = await generateAIContent(prompt);

    res.json({ suggestions });
  } catch (error) {
    res.status(500).json({ error: 'Error generating AI suggestions' });
  }
});

// Generate Department Report
router.post('/department/generate', authenticateToken, async (req, res) => {
  if (req.user.role !== 'manager') {
    return res.status(403).json({ error: 'Unauthorized' });
  }

  const { type, year, month, week } = req.body;
  const db = req.db;

  // 1. Fetch all reports for the period
  let query = '';
  let params = [];

  if (type === 'weekly') {
    query = 'SELECT * FROM weekly_reports WHERE year = ? AND week = ?';
    params = [year, week];
  } else if (type === 'monthly') {
    query = 'SELECT * FROM monthly_reports WHERE year = ? AND month = ?';
    params = [year, month];
  } else {
    query = 'SELECT * FROM annual_reports WHERE year = ?';
    params = [year];
  }

  db.all(query, params, async (err, reports) => {
    if (err) return res.status(500).json({ error: 'Database error' });
    if (!reports || reports.length === 0) {
      return res.status(404).json({ error: 'No reports found for this period' });
    }

    // 2. Aggregate content
    const achievements = reports.map(r => r.achievements || r.summary).join('\n');
    const challenges = reports.map(r => r.challenges || '').join('\n');

    // 3. Generate AI Summary
    const prompt = `
    Task: Generate a formal Department Level ${type} Report based on the following employee reports.
    
    Combined Achievements/Summary:
    ${achievements}
    
    Combined Challenges:
    ${challenges}
    
    Requirements:
    1. **Style**: Formal, professional, suitable for reporting to senior leadership. Use paragraph form, avoid bullet points for the main content.
    2. **Structure**:
       - **Overall Summary**: A concise paragraph summarizing the department's performance.
       - **Key Achievements**: Use "一是...；二是...；三是..." format to detail the main accomplishments. Each point should be a full paragraph.
       - **Challenges & Solutions**: A paragraph discussing challenges and proposed solutions.
       - **Next Steps**: A concluding paragraph on future focus.
    3. **Content**: Synthesize individual reports into a cohesive department narrative. Do not list individual employee names unless absolutely necessary for a major breakthrough.
    4. **Language**: **Simplified Chinese** only.
    5. **Format**: Markdown with appropriate headings (e.g., ### 工作综述, ### 重点工作进展, ### 存在问题与建议, ### 下一步计划).
    `;

    try {
      const aiContent = await generateAIContent(prompt);
      res.json({
        report: aiContent,
        title: `Department ${type.charAt(0).toUpperCase() + type.slice(1)} Report - ${year}`
      });
    } catch (e) {
      res.status(500).json({ error: 'AI generation failed' });
    }
  });
});

// Get count of weekly reports submitted for a specific week
router.get('/stats/weekly-count', authenticateToken, (req, res) => {
  if (req.user.role !== 'manager') {
    return res.status(403).json({ error: 'Unauthorized' });
  }

  const { year, month, week } = req.query;
  const db = req.db;

  if (!year || !month || !week) {
    return res.status(400).json({ error: 'Year, month and week are required' });
  }

  db.get(
    'SELECT COUNT(DISTINCT user_id) as count FROM weekly_reports WHERE year = ? AND month = ? AND week = ?',
    [year, month, week],
    (err, row) => {
      if (err) return res.status(500).json({ error: 'Database error' });
      res.json({ count: row ? row.count : 0 });
    }
  );
});

// Save Department Report
router.post('/department/save', authenticateToken, (req, res) => {
  if (req.user.role !== 'manager') {
    return res.status(403).json({ error: 'Unauthorized' });
  }

  const { type, year, month, week, content } = req.body;
  const db = req.db;

  db.run(
    `INSERT INTO department_reports (type, year, month, week, content) VALUES (?, ?, ?, ?, ?)`,
    [type, year, month, week, content],
    function (err) {
      if (err) return res.status(500).json({ error: 'Database error' });
      res.json({ message: 'Report saved successfully', id: this.lastID });
    }
  );
});

// Get Department Reports
router.get('/department', authenticateToken, (req, res) => {
  if (req.user.role !== 'manager') {
    return res.status(403).json({ error: 'Unauthorized' });
  }

  const db = req.db;
  db.all('SELECT * FROM department_reports ORDER BY created_at DESC', (err, reports) => {
    if (err) return res.status(500).json({ error: 'Database error' });
    res.json(reports);
  });
});

module.exports = router;
